import { Inject, Provide } from '@midwayjs/core';
import { query, transaction } from '../dbConnPool/mariadb'; // 引入查询函数
import {
  DepartmentDTO,
  NewDirectorIdDTO,
  UpdateDepartmentDTO,
} from '../dto/department';
import { PageDTO } from '../dto/page';
import { Caching } from '@midwayjs/cache-manager';
import { Context } from '@midwayjs/koa';
import { uuidv7 } from 'uuidv7';
import { BigIntService } from '../service/bigInt.service';
import { CachingFactory, MidwayCache } from '@midwayjs/cache-manager';
import { InjectClient } from '@midwayjs/core';

@Provide()
export class DepartmentDao {
  @Inject()
  ctx: Context;
  @Inject()
  bigIntService: BigIntService;
  //获取所有科室信息
  @Caching('redis', 'getDepartments')
  async getDepartments() {
    const sql = `
        SELECT
        id AS departmentId,
        name AS departmentName
        FROM
        department
        WHERE
        valid_flag = 1
    `;
    return await query(sql, [], false);
  }
  //查找科室表记录
  async findDepartment(field: string, value: string) {
    const sql = `
        SELECT
        COUNT(*) AS count
        FROM
        department
        WHERE
        ${field} = ? AND valid_flag = 1
        `;
    const sqlParams = [value];
    const result = await query(sql, sqlParams, false); // 设置 throwOnEmptyResult 为 false
    return result[0].count;
  }
  //查找该科室是否存在该用户
  async findDepartmentMember(userId: string, departmentId: string) {
    const sql = `
        SELECT
        COUNT(*) AS count
        FROM
        user
        WHERE
        department_id = ? AND user_id = ? AND valid_flag = 1
        `;
    const sqlParams = [departmentId, userId];
    const result = await query(sql, sqlParams, false); // 设置 throwOnEmptyResult 为 false
    return result[0].count;
  }
  //添加科室
  @InjectClient(CachingFactory, 'redis')
  cache: MidwayCache; //redis缓存
  async addDepartment(departmentName: DepartmentDTO['departmentName']) {
    const id = uuidv7();
    const userId = this.ctx.state.user.userId;
    return transaction(async conn => {
      const sql = `
        INSERT INTO
        department (id,user_id, name, created_at)
        VALUES
        (?, ?, ?, NOW())
        `;
      const sqlParams = [id, userId, departmentName];
      await query(sql, sqlParams, false, conn);
      const sql2 = `
        UPDATE
        user
        SET
        department_id = ?,
        director_flag = 1
        WHERE
        user_id = ?
        `;
      const sqlParams2 = [id, userId];
      await query(sql2, sqlParams2, false, conn);
      await this.cache.del('getDepartments');
      return true;
    });
  }
  //获取我的科室成员信息
  @Caching('redis', ctx => {
    if (
      ctx.methodArgs.length > 0 &&
      ctx.methodArgs[1].pageNum == 1 &&
      ctx.methodArgs[1].pageSize == 10
    ) {
      return `department:${ctx.methodArgs[0]}`;
    }
    return null;
  })
  async getMyDepartment(departmentId: string, pageDto: PageDTO) {
    const { pageNum, pageSize } = pageDto;
    return transaction(async conn => {
      const sql = `
        SELECT
        u.user_id AS userId,
        u.name,
        i.item_label AS identity,
        s.item_label AS sex,
        u.email,
        u.phone,
        u.director_flag AS directorFlag
        FROM
        user u
        LEFT JOIN
        sys_dict_item i ON u.identity = i.item_code AND i.dict_code = 'IDENTITY'
        LEFT JOIN
        sys_dict_item s ON u.sex = s.item_code AND s.dict_code = 'SEX'
        WHERE
        u.department_id = ?
        AND u.valid_flag = 1
        AND u.authentication_flag = 1
        ORDER BY
        u.created_at DESC
        LIMIT
        ?,?
    `;
      const sqlParams = [departmentId, (pageNum - 1) * pageSize, pageSize];
      const list = await query(sql, sqlParams, true, conn);
      const sql2 = `
        SELECT
        COUNT(*) AS total
        FROM
        user
        WHERE
        department_id = ?
        AND valid_flag = 1
        AND authentication_flag = 1
    `;
      const sqlParams2 = [departmentId];
      const total = await query(sql2, sqlParams2, true, conn);
      return this.bigIntService.bigInt({ total: total[0].total, list });
    });
  }
  //退出科室
  async exitDepartment(userId: string, departmentId: string) {
    const sql = `
        UPDATE
        user u
        LEFT JOIN
            authentication a ON u.user_id = a.user_id
        SET
        u.department_id = NULL,
        u.authentication_flag = 3,
        a.text = ?,
        a.valid_flag = 0
        WHERE
        u.user_id = ?
        AND u.department_id = ?
        `;
    const sqlParams = ['已经退出科室，需要重新认证', userId, departmentId];
    await query(sql, sqlParams, false);
    await this.cache.del(`department:${departmentId}`);
    return true;
  }
  //解散科室
  async delDepartment() {
    const departmentId = this.ctx.state.user.departmentId;
    const sql = `
        UPDATE
        department d
        LEFT JOIN
            user u ON d.id = u.department_id
        LEFT JOIN
            authentication a ON u.user_id = a.user_id
        SET
        d.valid_flag = 0,
        u.department_id = NULL,
        u.authentication_flag = 3,
        u.director_flag = 0,
        a.text = ?,
        a.valid_flag = 0
        WHERE
        d.id = ?
        `;
    const sqlParams = ['已经解散科室，需要重新认证', departmentId];
    await query(sql, sqlParams, false);
    await this.cache.del('getDepartments');
    await this.cache.del(`department:${departmentId}`);
    return true;
  }
  //主任转移权限
  async replaceDirector(
    newDirectorIdDto: NewDirectorIdDTO,
    departmentId: string
  ) {
    const userId = this.ctx.state.user.userId;
    const newDirectorId = newDirectorIdDto.newDirectorId;
    return transaction(async conn => {
      const sql = `
        UPDATE
        user u
        SET
        u.director_flag = 0
        WHERE
        u.department_id = ?
        AND u.user_id = ?
        AND u.director_flag = 1
        `;
      const sqlParams = [departmentId, userId];
      await query(sql, sqlParams, true, conn);
      const sql2 = `
        UPDATE
        user u
        SET
        u.director_flag = 1
        WHERE
        u.user_id = ?
        AND u.department_id = ?
        `;
      const sqlParams2 = [newDirectorId, departmentId];
      await query(sql2, sqlParams2, true, conn);
      await this.cache.del(`department:${departmentId}`);
      return true;
    });
  }
  //管理员获取所有科室信息
  async getAdminDepartments(pageDto: PageDTO) {
    const { pageNum, pageSize, type } = pageDto;
    return transaction(async conn => {
      const sql = `
            SELECT
                d.id AS departmentId,
                d.name AS departmentName,
                createdUser.name AS createdName,
                directorUser.name AS directorName,
                d.created_at AS createdAt,
                d.valid_flag AS validFlag
            FROM
                department d
            LEFT JOIN user createdUser ON createdUser.user_id = d.user_id
            LEFT JOIN user directorUser ON directorUser.department_id = d.id AND directorUser.director_flag = 1
            WHERE
                d.valid_flag = ?
            ORDER BY
                d.created_at DESC
            LIMIT
            ?,?
    `;
      const sqlParams = [type, (pageNum - 1) * pageSize, pageSize];
      const list = await query(sql, sqlParams, false, conn);
      const sql2 = `
        SELECT
        COUNT(*) AS total
        FROM
        department
        WHERE
        valid_flag = ?
    `;
      const sqlParams2 = [type];
      const total = await query(sql2, sqlParams2, false, conn);
      return this.bigIntService.bigInt({ total: total[0].total, list });
    });
  }
  //管理员修改科室信息
  async updateDepartment(updateDepartmentDto: UpdateDepartmentDTO) {
    const setClause = [];
    const sqlParams = [];
    return transaction(async conn => {
      for (const [key, value] of Object.entries(updateDepartmentDto)) {
        if (key === 'departmentId') {
          continue;
        }
        const snakeKey = this.camelToSnake(key);
        setClause.push(`${snakeKey} = ?`);
        sqlParams.push(value);
      }
      sqlParams.push(updateDepartmentDto.departmentId);
      const sql = `
        UPDATE
        department
        SET
        ${setClause.join(', ')}
        WHERE
        id = ?
    `;
      await query(sql, sqlParams, false, conn);
      if (updateDepartmentDto.validFlag == 0) {
        const sql1 = `
            UPDATE
            user u
            LEFT JOIN
                authentication a ON u.user_id = a.user_id
            SET
            u.department_id = NULL,
            u.director_flag = 0,
            u.authentication_flag = 3,
            a.text = ?,
            a.valid_flag = 0
            WHERE
            u.department_id = ?    
        `;
        const sqlParams1 = [
          '已经解散科室，需要重新认证',
          updateDepartmentDto.departmentId,
        ];
        await query(sql1, sqlParams1, false, conn);
      }
      await this.cache.del(`department:${updateDepartmentDto.departmentId}`);
      await this.cache.del('getDepartments');
      return true;
    });
  }
  //管理员查找科室成员
  async getDepartmentMember(departmentId: string) {
    const sql = `
        SELECT
        u.user_id AS userId,
        u.name,
        i.item_label AS identity,
        s.item_label AS sex,
        u.email,
        u.phone,
        a.link AS imgUrl,
        u.director_flag AS directorFlag
        FROM
        user u
        LEFT JOIN
        sys_dict_item i ON u.identity = i.item_code AND i.dict_code = 'IDENTITY'
        LEFT JOIN
        sys_dict_item s ON u.sex = s.item_code AND s.dict_code = 'SEX'
        LEFT JOIN
        authentication a ON u.user_id = a.user_id
        WHERE
        u.department_id = ?
        AND u.valid_flag = 1
        AND u.authentication_flag = 1
        ORDER BY
        u.created_at DESC
    `;
    const sqlParams = [departmentId];
    return await query(sql, sqlParams, false);
  }
  //管理员设置科室主任
  async adminSetDirector(userId: string, departmentId: string) {
    return transaction(async conn => {
      const sql = `
        UPDATE
        user u
        SET
        u.director_flag = 0
        WHERE
        u.department_id = ?
        `;
      const sqlParams = [departmentId];
      await query(sql, sqlParams, false, conn);
      const sql2 = `
        UPDATE
        user u
        SET
        u.director_flag = 1
        WHERE
        u.user_id = ?
        AND u.department_id = ?
        `;
      const sqlParams2 = [userId, departmentId];
      await query(sql2, sqlParams2, false, conn);
      await this.cache.del(`department:${departmentId}`);
      return true;
    });
  }
  // 工具函数：驼峰命名转下划线命名
  camelToSnake(str: string): string {
    return str.replace(/([a-z0-9])([A-Z])/g, '$1_$2').toLowerCase();
  }
}
